package com.oasystem.dao.jdbc.fund;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.oasystem.domain.PagingBean;
import com.oasystem.domain.fund.AppropriationApplication;
import com.oasystem.domain.user.User;
import com.oasystem.enums.EApproveStatus;
import com.oasystem.utils.DateUtils;
import com.oasystem.utils.PrimaryKeyGenerator;

public class AppropriationApplicationDao {
	private JdbcTemplate jdbcTemplate;

	public List<AppropriationApplication> findAll(Integer approverId,
			int start, int limit) {
		String sql = "SELECT * FROM AppropriationApplication WHERE approverid=? ORDER BY id DESC LIMIT ?,?";
		return jdbcTemplate.queryForList(sql, new Object[] { approverId, start,
				limit }, AppropriationApplication.class);
	}

	public void insert(AppropriationApplication appropriationApplication) {
		String sql = "INSERT INTO AppropriationApplication (id,applicantId,amount,reason,approver,approveStatus) VALUES (?,?,?,?,?,?)";
		jdbcTemplate
				.update(sql, new Object[] { PrimaryKeyGenerator.getUUID(),
						appropriationApplication.getApplicantId(),
						appropriationApplication.getAmount(),
						appropriationApplication.getReason(),
						appropriationApplication.getApprover().getId(),
						appropriationApplication.getApproveStatus().getCode() });
	}

	public void update(AppropriationApplication appropriationApplication) {
		String sql = "UPDATE AppropriationApplication SET approveStatus=? WHERE id=?";
		jdbcTemplate.update(sql, new Object[] {
				appropriationApplication.getApproveStatus().getCode(),
				appropriationApplication.getId() });
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public void appropriationApplicationHistory(String applicant,
			PagingBean<AppropriationApplication> pagingBean) {
		String sql = "SELECT * FROM appropriationApplication a,user u WHERE a.approver=u.id and applicantid=? order by applyTime desc LIMIT ?,?";
		pagingBean.setData(jdbcTemplate.query(sql, new Object[] { applicant,
				pagingBean.getCurrentRecordIndex(), pagingBean.getPageSize() },
				new RowMapper<AppropriationApplication>() {

					@Override
					public AppropriationApplication mapRow(ResultSet rs,
							int index) throws SQLException {
						AppropriationApplication application = new AppropriationApplication();
						application.setAmount(rs.getDouble("a.amount"));
						application.setApplyDate(DateUtils.format(
								"yyyy/MM/dd HH:mm:ss",
								rs.getTimestamp("a.applyTime")));
						User approver = new User();
						approver.setName(rs.getString("u.name"));
						application.setApprover(approver);
						application.setApproveStatus(EApproveStatus
								.getByStatus(rs.getString("a.approveStatus")));
						application.setReason(rs.getString("a.reason"));
						application.setRejectReason(rs.getString("a.rejectReason"));
						application.setId(rs.getString("a.id"));
						application.setApproveDate(rs
								.getString("a.approveTime"));
						return application;
					}

				}));
		sql = "SELECT COUNT(*) FROM appropriationApplication a,user u WHERE a.approver=u.id and applicantid=?";
		pagingBean.setAllRecordCount(jdbcTemplate.queryForInt(sql,
				new Object[] { applicant }));
	}

	public void appropriationApplicationListForCheck(String approverId,
			PagingBean<AppropriationApplication> pagingBean) {
		String sql = "select * from appropriationapplication a,user u where a.applicantId=u.id and approver=? order by applyTime desc LIMIT ?,?";
		pagingBean.setData(jdbcTemplate.query(sql, new Object[] { approverId,
				pagingBean.getCurrentRecordIndex(), pagingBean.getPageSize() },
				new RowMapper<AppropriationApplication>() {

					@Override
					public AppropriationApplication mapRow(ResultSet rs,
							int index) throws SQLException {
						AppropriationApplication application = new AppropriationApplication();
						application.setAmount(rs.getDouble("a.amount"));
						application.setApplyDate(DateUtils.format(
								"yyyy/MM/dd HH:mm:ss",
								rs.getTimestamp("a.applyTime")));
						application.setApplicantName(rs.getString("u.name"));
						application.setApproveStatus(EApproveStatus
								.getByStatus(rs.getString("a.approveStatus")));
						application.setReason(rs.getString("a.reason"));
						application.setRejectReason(rs.getString("a.rejectReason"));
						application.setId(rs.getString("a.id"));
						application.setApproveDate(DateUtils.format(
								"yyyy/MM/dd HH:mm:ss",
								rs.getTimestamp("a.approveTime")));
						return application;
					}

				}));
		sql = "SELECT COUNT(*) from appropriationapplication a,user u where a.applicantId=u.id and approver=?";
		pagingBean.setAllRecordCount(jdbcTemplate.queryForInt(sql,
				new Object[] { approverId }));
	}

	public void approveAppropriationApplication(
			String appropriationApplicationId) {
		String sql = "UPDATE appropriationapplication set approveStatus=?,approveTime=? where id=?";
		jdbcTemplate.update(
				sql,
				new Object[] { EApproveStatus.APPROVED.getCode(),
						DateUtils.getCurrentTimeString(),
						appropriationApplicationId });
	}

	public void rejectAppropriationApplication(
			String appropriationApplicationId, String reason) {
		String sql = "UPDATE appropriationapplication set approveStatus=?,approveTime=?,rejectReason=? where id=?";
		jdbcTemplate.update(sql, new Object[] { EApproveStatus.DENY.getCode(),
				DateUtils.getCurrentTimeString(), reason,
				appropriationApplicationId });
	}
	
	public double totalAppropriationOutcome() {
		String sql = "select sum(amount) from appropriationapplication where approveStatus='A'";
		return jdbcTemplate.queryForObject(sql, Double.class);
	}
}
